﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Xml;
using System.Xml.Xsl;

namespace Toenda.Foundation.Data {
	/// <summary>
	/// Class ExcelExport
    /// </summary>
	public class ExcelExport {
		#region "NESTED TYPES"
		// ---------------------------------------------------
		// NESTED TYPES
		// ---------------------------------------------------

		/// <summary>
		/// Initializes a new instance of the ExcelExportEventHandler class.
		/// </summary>
		public delegate void ExcelExportEventHandler(int currentColumnOrCellIndex, int currentRow, string text);
		/// <summary>
		/// Occurs when [excel export response].
		/// </summary>
		public event ExcelExportEventHandler ExcelExportResponse;

		/// <summary>
		/// Initializes a new instance of the ExcelExportFinishEventhandler class.
		/// </summary>
		public delegate void ExcelExportFinishEventhandler(bool successfull, string text);
		/// <summary>
		/// Occurs when [excel export finish].
		/// </summary>
		public event ExcelExportFinishEventhandler ExcelExportFinish;

		/// <summary>
		/// Provides Excel export return values.
		/// </summary>
		public enum ReturnValue {
			/// <summary>
			/// A value that indicates that Excel is not installed.
			/// </summary>
			ExcelNotInstalled = 0,
			/// <summary>
			/// Return message successfull.
			/// </summary>
			Successfull = 1,
			/// <summary>
			/// Return unknown error.
			/// </summary>
			UnknownError = 2,
			/// <summary>
			/// Return default export error.
			/// </summary>
			DefaultExportError = 3
		}

		/// <summary>
		/// Provides some specific Excel versions
		/// </summary>
		public enum ExcelVersion {
			/// <summary>
			/// Unknown version
			/// </summary>
			Unknown = 0,
			/// <summary>
			/// Version 10.0 (Microsft Excel 2002 / XP)
			/// </summary>
			Version10 = 1,
			/// <summary>
			/// Version 11.0 (Microsft Excel 2003 / MacOffice 2004)
			/// </summary>
			Version11 = 2,
			/// <summary>
			/// Version 12.0 (Microsft Excel 2007 / Excel:mac 2008)
			/// </summary>
			Version12 = 3
		}

		/// <summary>
		/// Class Document
		/// </summary>
		public class Document {
			private List<TableColumn> _columns;
			private List<DataContainer> _dc;

			/// <summary>
			/// Initializes a new instance of the <see cref="Document"/> class.
			/// </summary>
			public Document() {
				this._columns = new List<TableColumn>();
				this._dc = new List<DataContainer>();
			}

			/// <summary>
			/// Gets the column and row count.
			/// </summary>
			/// <value>The column and row count.</value>
			public int ColumnAndCellCount {
				get {
					int count = 0;

					// column styles
					count += this._columns.Count;

					// column itself
					count += this._columns.Count;

					// rows
					foreach(DataContainer row in this._dc) {
						count += row.Cells.Count;
					}

					return count;
				}
			}

			/// <summary>
			/// Gets or sets the sheet title.
			/// </summary>
			/// <value>The sheet title.</value>
			public string SheetTitle { get; set; }

			/// <summary>
			/// Gets the columns.
			/// </summary>
			/// <value>The columns.</value>
			public List<TableColumn> Columns {
				get { return this._columns; }
			}

			/// <summary>
			/// Gets the data.
			/// </summary>
			/// <value>The data.</value>
			public List<DataContainer> Data {
				get { return this._dc; }
			}
		}

		/// <summary>
		/// Class DataContainer
		/// </summary>
		public class DataContainer {
			private List<TableCell> _cell;

			/// <summary>
			/// Default Ctor
			/// </summary>
			public DataContainer() {
				this._cell = new List<TableCell>();
			}

			/// <summary>
			/// Get the cells
			/// </summary>
			public List<TableCell> Cells {
				get { return this._cell; }
			}
		}

		/// <summary>
		/// Class TableColumn
		/// </summary>
		public class TableColumn {
			private string _type;

			/// <summary>
			/// Gets or sets the name.
			/// </summary>
			/// <value>The name.</value>
			public string Name { get; set; }

			/// <summary>
			/// Gets or sets the width.
			/// </summary>
			/// <value>The width.</value>
			public int Width { get; set; }

			/// <summary>
			/// Gets or sets the type.
			/// </summary>
			/// <value>The type.</value>
			public string Type {
				get { return ( this._type.IsNullOrTrimmedEmpty() ? "String" : this._type ); }
				set { this._type = value; }
			}

			/// <summary>
			/// Gets or sets the value.
			/// </summary>
			/// <value>The value.</value>
			public string Value { get; set; }
		}

		/// <summary>
		/// Class TableCell
		/// </summary>
		public class TableCell {
			private string _type;

			/// <summary>
			/// Gets or sets the name.
			/// </summary>
			/// <value>The name.</value>
			public string Name { get; set; }

			/// <summary>
			/// Gets or sets the type.
			/// </summary>
			/// <value>The type.</value>
			public string Type {
				get { return ( this._type.IsNullOrTrimmedEmpty() ? "String" : this._type ); }
				set { this._type = value; }
			}

			/// <summary>
			/// Gets or sets the value.
			/// </summary>
			/// <value>The value.</value>
			public string Value { get; set; }
		}

		/// <summary>
		/// Class ExcelExportHelper
		/// </summary>
		public class ExcelExportHelper {
			StringBuilder sb;

			/// <summary>
			/// Initializes a new instance of the <see cref="ExcelExportHelper"/> class.
			/// </summary>
			public ExcelExportHelper() {
				sb = new StringBuilder();
			}

			/// <summary>
			/// Gets or sets the sheet title.
			/// </summary>
			/// <value>The sheet title.</value>
			public string SheetTitle {
				get;
				set;
			}

			/// <summary>
			/// GetCustomPropValue
			/// </summary>
			public delegate string GetCustomPropValue<T>(T r, string s);

			/// <summary>
			/// Applies the excel stylesheet.
			/// </summary>
			/// <param name="doc">The doc.</param>
			/// <param name="stylesheetPath">The stylesheet path.</param>
			/// <returns></returns>
			public static string ApplyExcelStylesheet(XmlDocument doc, string stylesheetPath) {
				string answer = null;
				XslCompiledTransform xslt = new XslCompiledTransform();

				xslt.Load(stylesheetPath);
				
				StringBuilder sb = new StringBuilder();
				StringWriter sw = new StringWriter(sb);
				
				xslt.Transform(doc, null, sw);
				answer = sb.ToString();

				return answer;
			}

			/// <summary>
			/// Gets the property value.
			/// </summary>
			/// <typeparam name="T"></typeparam>
			/// <param name="o">The o.</param>
			/// <param name="name">The name.</param>
			/// <returns></returns>
			public static string GetPropertyValue<T>(T o, string name) {
				//string answer = null;
				//List<PropertyInfo> propList = new List<PropertyInfo>(typeof(T).GetProperties());
				//PropertyInfo prop = propList.Find(delegate(PropertyInfo p) { return p.Name == name; });
				//object value = prop.GetValue(o, null);
				
				//if(value != null) {
				//    answer = value.ToString();
				//}

				//return answer;

				return "";
			}

			/// <summary>
			/// Adds the title.
			/// </summary>
			/// <param name="fname">The fname.</param>
			public void AddTitle(string fname) {
				sb.AppendFormat("<Field><FieldName>{0}</FieldName><FieldValue></FieldValue><title>true</title></Field>", fname);
			}

			/// <summary>
			/// Adds the field.
			/// </summary>
			/// <typeparam name="T"></typeparam>
			/// <param name="rl">The rl.</param>
			/// <param name="fdesc">The fdesc.</param>
			/// <param name="fname">The fname.</param>
			public void AddField<T>(List<T> rl, string fdesc, string fname) {
				AddField(rl, fdesc, fname, false);
			}

			/// <summary>
			/// Adds the field.
			/// </summary>
			/// <typeparam name="T"></typeparam>
			/// <param name="rl">The rl.</param>
			/// <param name="fdesc">The fdesc.</param>
			/// <param name="fname">The fname.</param>
			/// <param name="title">if set to <c>true</c> [title].</param>
			public void AddField<T>(List<T> rl, string fdesc, string fname, bool title) {
				AddField(rl, fdesc, fname, title, new GetCustomPropValue<T>(GetPropertyValue<T>));
			}

			/// <summary>
			/// Adds the field.
			/// </summary>
			/// <typeparam name="T"></typeparam>
			/// <param name="rl">The rl.</param>
			/// <param name="fdesc">The fdesc.</param>
			/// <param name="fname">The fname.</param>
			/// <param name="title">if set to <c>true</c> [title].</param>
			/// <param name="getValue">The get value.</param>
			public void AddField<T>(List<T> rl, string fdesc, string fname, bool title, GetCustomPropValue<T> getValue) {
				sb.AppendFormat("<Field> <FieldName>{0}</FieldName>", fdesc);
				
				foreach(T r in rl) {
					sb.AppendFormat("<FieldValue>{0}</FieldValue>", getValue(r, fname));
				}

				if(title) {
					sb.Append("<title>true</title>");
				}

				sb.AppendFormat("</Field>");
			}

			/// <summary>
			/// Gets the XML.
			/// </summary>
			/// <returns></returns>
			public XmlDocument GetXml() {
				XmlDocument xmlOutputDoc = new XmlDocument();
				StringBuilder tmp = new StringBuilder();
				
				tmp.Append("<data>");
				tmp.Append("<FieldGroup>");
				tmp.Append(sb);
				tmp.AppendFormat("<SheetTitle>{0}</SheetTitle>", this.SheetTitle);
				tmp.Append("</FieldGroup>");
				tmp.Append("</data>");
				xmlOutputDoc.LoadXml(tmp.ToString());

				return xmlOutputDoc;
			}
		}

		#endregion

		#region "INTERNAL VARIABLES"
		// ---------------------------------------------------
		// INTERNAL VARIABLES
		// ---------------------------------------------------
		#endregion

		#region "CONSTRUCTORS"
		// ---------------------------------------------------
		// CONSTRUCTORS
		// ---------------------------------------------------

		/// <summary>
		/// Default Ctor, Microsoft Excel must be installed.
		/// </summary>
		public ExcelExport() {
		}

		#endregion

		#region "INTERFACE IMPLEMENTATIONS"
		// ---------------------------------------------------
		// INTERFACE IMPLEMENTATIONS
		// ---------------------------------------------------
		#endregion

		#region "PROPERTIES"
		// ---------------------------------------------------
		// PROPERTIES
		// ---------------------------------------------------
		#endregion

		#region "EVENTS"
		// ---------------------------------------------------
		// EVENTS
		// ---------------------------------------------------
		#endregion

		#region "PRIVATE MEMBERS"
		// ---------------------------------------------------
		// PRIVATE MEMBERS
		// ---------------------------------------------------

		/// <summary>
		/// Create a OLEDB connectionstring for the Excel connection
		/// </summary>
		/// <param name="filename">The filename (.xls).</param>
		/// <returns>The connectionstring.</returns>
		private static string BuidExcelConnectionString(string filename) {
			return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0";
		}

		/// <summary>
		/// Buids the excel connection string.
		/// </summary>
		/// <param name="filename">The filename.</param>
		/// <param name="version">The version.</param>
		/// <returns></returns>
		private static string BulidExcelConnectionString(string filename, ExcelVersion version) {
			StringBuilder str = new StringBuilder();

			str.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
			str.Append(filename);
			str.Append(";Extended Properties=");

			switch(version) {
				case ExcelVersion.Version10:
					str.Append("Excel 10.0");
					break;

				case ExcelVersion.Version11:
					str.Append("Excel 11.0");
					break;

				case ExcelVersion.Version12:
					str.Append("Excel 12.0");
					break;

				case ExcelVersion.Unknown:
				default:
					str.Append("Excel 8.0");
					break;
			}

			return str.ToString();
		}

		/// <summary>
		/// Write the scheme of a table into a Excel table
		/// </summary>
		/// <param name="table">The table.</param>
		/// <param name="filename">The Excel filename.</param>
		private static ReturnValue WriteTableSchemaToExcelFile(DataTable table, string filename) {
			try {
				// open Excel in background
				object excel = Activator.CreateInstance(
					Type.GetTypeFromProgID("Excel.Application")
				);

				// get maps
				object books = excel.GetType().InvokeMember(
					"Workbooks",
					BindingFlags.IgnoreCase
					| BindingFlags.GetProperty
					| BindingFlags.OptionalParamBinding,
					null,
					excel,
					new object[0]
				);

				// create new map
				object book = books.GetType().InvokeMember(
					"Add",
					BindingFlags.InvokeMethod
					| BindingFlags.OptionalParamBinding,
					null,
					books,
					new object[0]
				);

				// get tables
				object sheets = book.GetType().InvokeMember(
					"Sheets",
					BindingFlags.IgnoreCase
					| BindingFlags.GetProperty
					| BindingFlags.OptionalParamBinding,
					null,
					book,
					new object[0]
				);

				// create new table
				object sheet = sheets.GetType().InvokeMember(
					"Add",
					BindingFlags.InvokeMethod
					| BindingFlags.OptionalParamBinding,
					null,
					sheets,
					new object[0]
				);

				// set the name of the table
				sheet.GetType().InvokeMember(
					"Name",
					BindingFlags.SetProperty,
					null,
					sheet,
					new object[1] { table.TableName }
				);

				// counter
				int i = 0;

				// get columns
				foreach(DataColumn column in table.Columns) {
					i++;

					// Feldnamen einfügen
					object range = sheet.GetType().InvokeMember(
						"Cells",
						BindingFlags.GetProperty
						| BindingFlags.OptionalParamBinding,
						null,
						sheet,
						new object[2] { 1, i }
					);

					range.GetType().InvokeMember(
						"Value",
						BindingFlags.SetProperty
						| BindingFlags.OptionalParamBinding,
						null,
						range,
						new object[1] { column.ColumnName }
					);
				}

				// save document
				book.GetType().InvokeMember(
					"SaveAs",
					BindingFlags.InvokeMethod
					| BindingFlags.OptionalParamBinding,
					null,
					book,
					new object[1] { filename }
				);

				// free COM links
				Marshal.ReleaseComObject(sheet);
				Marshal.ReleaseComObject(sheets);
				Marshal.ReleaseComObject(book);
				Marshal.ReleaseComObject(books);

				// quit Excel
				excel.GetType().InvokeMember(
					"Quit",
					BindingFlags.InvokeMethod,
					null,
					excel,
					new object[0]
				);

				// free Excel object
				Marshal.ReleaseComObject(excel);

				return ReturnValue.Successfull;
			}
			catch(Exception) {
				return ReturnValue.ExcelNotInstalled;
			}
		}

		/// <summary>
		/// Create a Excel document from a table
		/// </summary>
		/// <param name="table">The table.</param>
		/// <param name="filename">The target filename.</param>
		private static ReturnValue FillExcelSheet(DataTable table, string filename) {
			try {
				WriteTableSchemaToExcelFile(table, filename);

				// create connection string
				string connectionString = BuidExcelConnectionString(filename);

				OleDbConnection connection = new OleDbConnection(connectionString);
				connection.Open();

				StringBuilder parameterBuilder = new StringBuilder(") VALUES (");

				int columnCount = table.Columns.Count;

				StringBuilder builder = new StringBuilder("INSERT INTO [");
				builder.Append(table.TableName);
				builder.Append("$] (");

				for(int i = 0; i < columnCount; i++) {
					builder.Append(table.Columns[i].ColumnName);

					parameterBuilder.Append("?");

					if(i < ( columnCount - 1 )) {
						builder.Append(",");
						parameterBuilder.Append(",");
					}
				}

				builder.Append(parameterBuilder.ToString());
				builder.Append(")");

				string insertStatement = builder.ToString();

				foreach(DataRow row in table.Rows) {
					OleDbCommand command = new OleDbCommand(
						insertStatement,
						connection
					);

					foreach(DataColumn column in table.Columns) {
						command.Parameters.Add(
							new OleDbParameter(
								column.ColumnName,
								row[column]
							)
						);
					}

					command.ExecuteNonQuery();
				}

				connection.Close();

				return ReturnValue.Successfull;
			}
			catch(Exception) {
				return ReturnValue.ExcelNotInstalled;
			}
		}

		#endregion

		#region "PROTECTED MEMBERS"
		// ---------------------------------------------------
		// PROTECTED MEMBERS
		// ---------------------------------------------------
		#endregion

		#region "PUBLIC MEMBERS"
		// ---------------------------------------------------
		// PUBLIC MEMBERS
		// ---------------------------------------------------

		/// <summary>
		/// Exports the data list to worksheet.
		/// </summary>
		/// <param name="dataSource">The data source.</param>
		/// <param name="fileName">Name of the file.</param>
		/// <returns></returns>
		public static ReturnValue ExportDataListToWorksheet(Document dataSource, string fileName) {
			ExcelExport export = new ExcelExport();
			return export.ExportDocument(dataSource, fileName, false);
		}

		/// <summary>
		/// Exports the data list to worksheet.
		/// </summary>
		/// <param name="dataSource">The data source.</param>
		/// <param name="fileName">Name of the file.</param>
		/// <param name="withEvents">if set to <c>true</c> [with events].</param>
		/// <returns></returns>
		public ReturnValue ExportDocument(Document dataSource, string fileName, bool withEvents) {
			try {
				// XML-Schreiber erzeugen
				XmlTextWriter writer = new XmlTextWriter(fileName, Encoding.UTF8);

				// Ausgabedatei für bessere Lesbarkeit formatieren (einrücken etc.)
				writer.Formatting = Formatting.Indented;

				// <?xml version="1.0"?>
				writer.WriteStartDocument();

				// <?mso-application progid="Excel.Sheet"?>
				writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

				// <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
				writer.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");

				// Definition der Namensräume schreiben 
				writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
				writer.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
				writer.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
				writer.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

				// <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
				writer.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

				// Dokumenteingeschaften schreiben
				writer.WriteElementString("Author", Environment.UserName);
				writer.WriteElementString("LastAuthor", Environment.UserName);
				writer.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
				writer.WriteElementString("Company", "Unknown");
				writer.WriteElementString("Version", "11.9999");

				// </DocumentProperties>
				writer.WriteEndElement();

				// <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
				writer.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

				// Arbeitsmappen-Einstellungen schreiben
				writer.WriteElementString("WindowHeight", "13170");
				writer.WriteElementString("WindowWidth", "17580");
				writer.WriteElementString("WindowTopX", "120");
				writer.WriteElementString("WindowTopY", "60");
				writer.WriteElementString("ProtectStructure", "False");
				writer.WriteElementString("ProtectWindows", "False");

				// </ExcelWorkbook>
				writer.WriteEndElement();

				// <Styles>
				writer.WriteStartElement("Styles");

				// <Style ss:ID="Default" ss:Name="Normal">
				writer.WriteStartElement("Style");
				writer.WriteAttributeString("ss", "ID", null, "Default");
				writer.WriteAttributeString("ss", "Name", null, "Normal");

				// <Alignment ss:Vertical="Bottom"/>
				writer.WriteStartElement("Alignment");
				writer.WriteAttributeString("ss", "Vertical", null, "Bottom");
				writer.WriteEndElement();

				// Verbleibende Sytle-Eigenschaften leer schreiben
				writer.WriteElementString("Borders", null);
				writer.WriteElementString("Font", null);
				writer.WriteElementString("Interior", null);
				writer.WriteElementString("NumberFormat", null);
				writer.WriteElementString("Protection", null);

				// </Style>
				writer.WriteEndElement();

				//-------------------

				// <Style ss:ID="s21">
				writer.WriteStartElement("Style");
				writer.WriteAttributeString("ss", "ID", null, "s21");
				writer.WriteElementString("Font", null);

				// </Style>
				writer.WriteEndElement();

				//-------------------

				// <Style ss:ID="s24">
				writer.WriteStartElement("Style");
				writer.WriteAttributeString("ss", "ID", null, "s24");

				// <Font x:Family="Swiss" ss:Color="#FFFFFF"/>
				writer.WriteStartElement("Font");
				writer.WriteAttributeString("x", "Family", null, "Swiss");
				writer.WriteAttributeString("ss", "Color", null, "#FFFFFF");
				writer.WriteEndElement();

				// <Interior ss:Color="#000000" ss:Pattern="Solid"/>
				writer.WriteStartElement("Interior");
				writer.WriteAttributeString("ss", "Color", null, "#000000");
				writer.WriteAttributeString("ss", "Pattern", null, "Solid");
				writer.WriteEndElement();

				// </Style>
				writer.WriteEndElement();

				//-------------------

				// <Style ss:ID="s22">
				writer.WriteStartElement("Style");
				writer.WriteAttributeString("ss", "ID", null, "s22");

				// <Font x:Family="Swiss" ss:Size="14" ss:Bold="1"/>
				writer.WriteStartElement("Font");
				writer.WriteAttributeString("x", "Family", null, "Swiss");
				writer.WriteAttributeString("ss", "Size", null, "14");
				writer.WriteAttributeString("ss", "Bold", null, "1");
				writer.WriteEndElement();

				// </Style>
				writer.WriteEndElement();

				//-------------------

				// <Style ss:ID="s25">
				writer.WriteStartElement("Style");
				writer.WriteAttributeString("ss", "ID", null, "s25");

				// <Font x:Family="Swiss" ss:Color="#FFFFFF" ss:Bold="1"/>
				writer.WriteStartElement("Font");
				writer.WriteAttributeString("x", "Family", null, "Swiss");
				writer.WriteAttributeString("ss", "Color", null, "#FFFFFF");
				writer.WriteAttributeString("ss", "Bold", null, "1");
				writer.WriteEndElement();

				// <Interior ss:Color="#000000" ss:Pattern="Solid"/>
				writer.WriteStartElement("Interior");
				writer.WriteAttributeString("ss", "Color", null, "#000000");
				writer.WriteAttributeString("ss", "Pattern", null, "Solid");
				writer.WriteEndElement();

				// </Style>
				writer.WriteEndElement();

				//-------------------

				// <Style ss:ID="s26">
				writer.WriteStartElement("Style");
				writer.WriteAttributeString("ss", "ID", null, "s26");

				// <Font x:Family="Swiss" ss:Size="12" ss:Bold="1"/>
				writer.WriteStartElement("Font");
				writer.WriteAttributeString("x", "Family", null, "Swiss");
				writer.WriteAttributeString("ss", "Size", null, "12");
				writer.WriteAttributeString("ss", "Bold", null, "1");
				writer.WriteEndElement();

				// </Style>
				writer.WriteEndElement();

				//-------------------

				// </Styles>
				writer.WriteEndElement();

				// <Worksheet ss:Name="xxx">
				writer.WriteStartElement("Worksheet");
				writer.WriteAttributeString("ss", "Name", null, dataSource.SheetTitle);

				// <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
				//int cellCount = 0;
				//int rowCount = 0;

				//if(dataSource.Data.Count > 0) {
				//    cellCount = dataSource[0].Cells.Count;

				//    if(dataSource[0].Columns.Count > 0) {
				//        cellCount++;
				//    }
				//}

				//rowCount = dataSource.Count;

				//if(dataSource.Count > 0) {
				//    if(dataSource[0].Columns.Count > 0) {
				//        rowCount++;
				//    }
				//}

				writer.WriteStartElement("Table");
				//writer.WriteAttributeString("ss", "ExpandedColumnCount", null, cellCount.ToString());
				//writer.WriteAttributeString("ss", "ExpandedRowCount", null, rowCount.ToString());
				//writer.WriteAttributeString("x", "FullColumns", null, "1");
				//writer.WriteAttributeString("x", "FullRows", null, "1");
				//writer.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");

				// Alle Zeilen der Datenquelle durchlaufen
				int count = 0;

				// define columns
				foreach(TableColumn col in dataSource.Columns) {
					// <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="250"/>
					writer.WriteStartElement("Column");
					writer.WriteAttributeString("ss", "StyleID", null, "s21");
					writer.WriteAttributeString("ss", "AutoFitWidth", null, "0");
					writer.WriteAttributeString("ss", "Width", null, col.Width.ToString());

					// </Column>
					writer.WriteEndElement();

					if(withEvents) {
						this.ExcelExportResponse(count, 0, "Write Column-Styles...");
						count++;
					}
				}

				// title
				writer.WriteStartElement("Row");
				writer.WriteAttributeString("ss", "AutoFitHeight", null, "0");
				writer.WriteAttributeString("ss", "Height", null, "18");
				writer.WriteStartElement("Cell");
				writer.WriteAttributeString("ss", "StyleID", null, "s22");
				writer.WriteStartElement("Data");
				writer.WriteAttributeString("ss", "Type", null, "String");
				writer.WriteValue(dataSource.SheetTitle);
				writer.WriteEndElement();
				writer.WriteEndElement();
				writer.WriteEndElement();

				// empty row
				writer.WriteStartElement("Row");
				writer.WriteStartElement("Cell");
				writer.WriteAttributeString("ss", "StyleID", null, "Default");
				writer.WriteEndElement();
				writer.WriteEndElement();

				// fill values to columns
				// <Row ss:StyleID="s24">
				writer.WriteStartElement("Row");
				writer.WriteAttributeString("ss", "StyleID", null, "s24");

				foreach(TableColumn col in dataSource.Columns) {
					// <Cell ss:StyleID="s25">
					writer.WriteStartElement("Cell");
					writer.WriteAttributeString("ss", "StyleID", null, "s25");

					// <Data ss:Type="String">xxx</Data>
					writer.WriteStartElement("Data");
					writer.WriteAttributeString("ss", "Type", null, col.Type);

					// Zelleninhakt schreiben
					writer.WriteValue(col.Value);

					// </Data>
					writer.WriteEndElement();

					// </Cell>
					writer.WriteEndElement();

					if(withEvents) {
						this.ExcelExportResponse(count, 0, "Write Column itself...");
						count++;
					}
				}

				// </Row>
				writer.WriteEndElement();

				int rowCount = 0;

				foreach(DataContainer row in dataSource.Data) {
					//if(count == 0) {
					//    // define columns
					//    foreach(TableColumn col in row.Columns) {
					//        // <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="250"/>
					//        writer.WriteStartElement("Column");
					//        writer.WriteAttributeString("ss", "StyleID", null, "s21");
					//        writer.WriteAttributeString("ss", "AutoFitWidth", null, "0");
					//        writer.WriteAttributeString("ss", "Width", null, col.Width.ToString());

					//        // </Column>
					//        writer.WriteEndElement();
					//    }

					//    // fill values to columns
					//    // <Row>
					//    writer.WriteStartElement("Row");

					//    foreach(TableColumn col in row.Columns) {
					//        // <Cell ss:StyleID="s24">
					//        writer.WriteStartElement("Cell");
					//        writer.WriteAttributeString("ss", "StyleID", null, "s21");

					//        // <Data ss:Type="String">xxx</Data>
					//        writer.WriteStartElement("Data");
					//        writer.WriteAttributeString("ss", "Type", null, col.Type);

					//        // Zelleninhakt schreiben
					//        writer.WriteValue(col.Value);

					//        // </Data>
					//        writer.WriteEndElement();

					//        // </Cell>
					//        writer.WriteEndElement();
					//    }

					//    // </Row>
					//    writer.WriteEndElement();
					//}

					// <Row>
					writer.WriteStartElement("Row");

					// Alle Zellen der aktuellen Zeile durchlaufen
					foreach(TableCell cell in row.Cells) {
						// <Cell>
						writer.WriteStartElement("Cell");

						// <Data ss:Type="String">xxx</Data>
						writer.WriteStartElement("Data");
						writer.WriteAttributeString("ss", "Type", null, cell.Type);

						// Zelleninhakt schreiben
						writer.WriteValue(cell.Value);

						// </Data>
						writer.WriteEndElement();

						// </Cell>
						writer.WriteEndElement();

						if(withEvents) {
							this.ExcelExportResponse(count, rowCount, "Write Cell inside row (" + rowCount.ToString() + ")...");
							count++;
						}
					}

					// </Row>
					writer.WriteEndElement();

					if(withEvents) {
						this.ExcelExportResponse(count, rowCount, "Row " + rowCount.ToString() + " writen, Write next row...");
						rowCount++;
					}
				}

				// empty row
				writer.WriteStartElement("Row");
				writer.WriteStartElement("Cell");
				writer.WriteAttributeString("ss", "StyleID", null, "Default");
				writer.WriteEndElement();
				writer.WriteEndElement();

				// add number of entries
				writer.WriteStartElement("Row");
				writer.WriteStartElement("Cell");
				writer.WriteStartElement("Data");
				writer.WriteAttributeString("ss", "Type", null, "String");
				writer.WriteValue(Language.EntryCount + ": " + dataSource.Data.Count.ToString());
				writer.WriteEndElement();
				writer.WriteEndElement();
				writer.WriteEndElement();
				writer.WriteEndElement();

				// <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
				writer.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");

				// Seiteneinstellungen schreiben
				writer.WriteStartElement("PageSetup");
				writer.WriteStartElement("Header");
				writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
				writer.WriteEndElement();
				writer.WriteStartElement("Footer");
				writer.WriteAttributeString("x", "Margin", null, "0.4921259845");
				writer.WriteEndElement();
				writer.WriteStartElement("PageMargins");
				writer.WriteAttributeString("x", "Bottom", null, "0.984251969");
				writer.WriteAttributeString("x", "Left", null, "0.78740157499999996");
				writer.WriteAttributeString("x", "Right", null, "0.78740157499999996");
				writer.WriteAttributeString("x", "Top", null, "0.984251969");
				writer.WriteEndElement();
				writer.WriteEndElement();

				// <Selected/>
				writer.WriteElementString("Selected", null);

				// <Panes>
				writer.WriteStartElement("Panes");

				// <Pane>
				writer.WriteStartElement("Pane");

				// Bereichseigenschaften schreiben
				writer.WriteElementString("Number", "1");
				writer.WriteElementString("ActiveRow", "1");
				writer.WriteElementString("ActiveCol", "1");

				// </Pane>
				writer.WriteEndElement();

				// </Panes>
				writer.WriteEndElement();

				// <ProtectObjects>False</ProtectObjects>
				writer.WriteElementString("ProtectObjects", "False");

				// <ProtectScenarios>False</ProtectScenarios>
				writer.WriteElementString("ProtectScenarios", "False");

				// </WorksheetOptions>
				writer.WriteEndElement();

				// </Worksheet>
				writer.WriteEndElement();

				// </Workbook>
				writer.WriteEndElement();

				// Datei auf Festplatte schreiben
				writer.Flush();
				writer.Close();

				if(withEvents) {
					this.ExcelExportFinish(true, "ExcelExport finished successfully.");
				}

				return ReturnValue.Successfull;
			}
			catch(Exception) {
				if(withEvents) {
					this.ExcelExportFinish(false, "ExcelExport finished not successfully.");
				}

				return ReturnValue.DefaultExportError;
			}
		}

		/// <summary>
		/// Exports to XML.
		/// </summary>
		/// <param name="dataSource">The data source.</param>
		/// <param name="sheetTitle">The sheet title.</param>
		/// <returns></returns>
		public static XmlDocument ExportToXml(List<DataContainer> dataSource, string sheetTitle) {
			ExcelExportHelper excExp = new ExcelExportHelper();
			excExp.SheetTitle = sheetTitle;

			excExp.AddTitle("test");

			foreach(DataContainer dc in dataSource) {
				excExp.AddField(dc.Cells, "testTitle", "fname", false);
			}

			return excExp.GetXml();
		}

		/// <summary>
		/// Exports to excel.
		/// </summary>
		/// <param name="dataSource">The data source.</param>
		/// <param name="sheetTitle">The sheet title.</param>
		/// <param name="stylesheetPath">The stylesheet path.</param>
		/// <returns></returns>
		public static string ExportToExcel(List<DataContainer> dataSource, string sheetTitle, string stylesheetPath) {
			XmlDocument xmlResult = ExportToXml(dataSource, sheetTitle);
			return ExcelExportHelper.ApplyExcelStylesheet(xmlResult, stylesheetPath);
		}

		#endregion
	}
}
